package tsli.acm.dao;

import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class GACommissionPositiveDAO {
	private Database db;
    

	 public GACommissionPositiveDAO(Database db) {
		 this.db = db;
	 }
	 
	 public List<Map<String, Object>> getAllAgentStructure() {
			String vaSql = "SELECT * "
					+ "FROM tsli_ga_COMMISSION_POSITIVE";
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		}
	 
	 public List<Map<String, Object>> getAllGMByQuarterly(String paPeNoBm,String paQuarterly, String paYear ) {
		 
		 String paMonth1 = "";
		 String paMonth2 = "";
		 String paMonth3 = "";
		 
		 	if(paQuarterly.equals("1")){
		 		paMonth1 = paYear+"01";
		 		paMonth2 = paYear+"02";
		 		paMonth3 = paYear+"03";
		 	}else if(paQuarterly.equals("2")){
		 		paMonth1 = paYear+"04";
		 		paMonth2 = paYear+"05";
		 		paMonth3 = paYear+"06";
		 	}else if(paQuarterly.equals("3")){
		 		paMonth1 = paYear+"07";
		 		paMonth2 = paYear+"08";
		 		paMonth3 = paYear+"09";
		 	}else if(paQuarterly.equals("4")){
		 		paMonth1 = paYear+"10";
		 		paMonth2 = paYear+"11";
		 		paMonth3 = paYear+"12";
		 	}
		 	
		 	String vaSql = "SELECT  c.pe_no,  SUM(c.afyp)   , SUM(c.aryp2), SUM(c.afyp+c.aryp2) as PC,"
		 			+ "		CASE "
		 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 0 AND SUM(c.afyp+c.aryp2) <= 2399999 THEN 0.00"
		 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 2400000 AND SUM(c.afyp+c.aryp2) <= 4799999 THEN 0.005"
		 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 4800000 AND SUM(c.afyp+c.aryp2) <= 7199999 THEN 0.01"
		 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 7200000 AND SUM(c.afyp+c.aryp2) <= 9599999 THEN 0.015"
		 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 9600000 AND SUM(c.afyp+c.aryp2) <= 11999999 THEN 0.02"
		 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 12000000  THEN 0.025"
		 			+ "		  ELSE 0.00"
		 			+ "		END AS BONUS,"
		 			+ "		CASE"
		 			+ "		 WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) >= 0 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) <= 59.99 THEN 0.00"
		 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) >= 60 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) <= 74.99 THEN 0.50"
		 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) >= 75 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) <= 79.99 THEN 0.90"
		 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no= p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) >= 80 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) <= 84.99 THEN 1.00"
		 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) >= 85 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) <= 89.99 THEN 1.10"
		 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) >= 90 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
		 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
		 			+ "		AND p.clos_ym  IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
		 			+ "		) <= 100 THEN 1.20"
		 			+ "		 ELSE -1"
		 			+ "		END AS PERSISTENCY_CREDIT,"
		 			+ "		(SELECT fst_enco_mms FROM tsli_ga_agent_structure a WHERE  c.pe_no = a.pe_no"
		 			+ "	AND a.clos_ym  IN ('"+paMonth3+"')  ) AS EXPERIENCE ,"
		 			+ "		(SELECT credit FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paMonth3+"') ) AS CREDIT,"
		 			+ "		(SELECT up_level_yn FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paMonth3+"') ) AS UP_LEVEL_YN,"
		 			+ "		(SELECT reg_dtm FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paMonth3+"') ) AS REG_DTM,"
		 			+ "		(SELECT pe_no_sm FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paMonth3+"') ) AS PE_NO_SM,"
		 			+ "		(SELECT pe_no_mm FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paMonth3+"') ) AS PE_NO_MM,"
		 			+ "		(SELECT pe_no_bm FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paMonth3+"') ) AS PE_NO_BM"
		 			+ "		FROM (SELECT a.PE_NO_MM AS PE_NO, (SUM(a.afYP)-SUM(b.aFYP)) AS AFYP ,(SUM(a.arYP2)-SUM(b.arYP2)) AS ARYP2, ( (SUM(a.afYP)-SUM(b.aFYP))+(SUM(a.arYP2)-SUM(b.arYP2)) ) as PC_AVP"
		 			+ "		      FROM TSLI_GA_COMMISSION_POSITIVE a"
		 			+ "                                        JOIN TSLI_GA_COMMISSION_NEGATIVE b"
		 			+ "                                        ON a.PE_NO_col = b.PE_NO_col"
		 			+ "		      WHERE a.PE_NO_MM IN (SELECT PE_NO"
		 			+ "		    		  FROM TSLI_GA_AGENT_STRUCTURE "
		 			+ "		                                WHERE PE_NO_BM = TIER1"
		 			+ "                                                                  AND PE_NO_BM = '"+paPeNoBm+"'"
		 			+ "		                                GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD) "
		 			+ "                                        AND b.CLOS_YM = a.CLOS_YM"
		 			+ "                                        AND b.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
		 			+ "		      GROUP BY a.PE_NO_MM"
		 			+ "		      Union All"
		 			+ "		      SELECT a.PE_NO_MM AS PE_NO, (SUM(a.afYP)-SUM(b.aFYP)) AS AFYP ,(SUM(a.arYP2)-SUM(b.arYP2)) AS ARYP2, ( (SUM(a.afYP)-SUM(b.aFYP))+(SUM(a.arYP2)-SUM(b.arYP2)) ) as PC_AVP"
		 			+ "		      FROM TSLI_GA_COMMISSION_POSITIVE a"
		 			+ "                                        JOIN TSLI_GA_COMMISSION_NEGATIVE b"
		 			+ "                                        ON a.PE_NO_col = b.PE_NO_col"
		 			+ "		      WHERE a.PE_NO_MM IS NULL"
		 			+ "                                        AND a.PE_NO_BM = '"+paPeNoBm+"'"
		 			+ "                                        AND b.CLOS_YM = a.CLOS_YM"
		 			+ "                                        AND b.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
		 			+ "		      GROUP BY a.PE_NO_MM "
		 			+ "		      Union All"
		 			+ "                                  SELECT a.PE_NO_col AS PE_NO,(SUM(a.afYP)-SUM(b.aFYP)) AS AFYP,(SUM(a.arYP2)-SUM(b.arYP2)) AS ARYP2,0.1*( (SUM(a.afYP)-SUM(b.aFYP))+(SUM(a.arYP2)-SUM(b.arYP2)) ) as PC_AVP"
		 			+ "                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
		 			+ "                                        JOIN TSLI_GA_COMMISSION_NEGATIVE b"
		 			+ "                                        ON a.PE_NO_col = b.PE_NO_col"
		 			+ "                                  WHERE a.pe_no_col = b.pe_no_col"
		 			+ "                                        and a.PE_NO_col IN (SELECT PE_NO"
		 			+ "                                                          FROM TSLI_GA_AGENT_STRUCTURE "
		 			+ "                                                          WHERE PE_NO_BM = TIER2"
		 			+ "                                                                AND PE_NO_BM = '"+paPeNoBm+"'"
		 			+ "                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
		 			+ "                                        AND b.CLOS_YM = a.CLOS_YM"
		 			+ "                                        AND b.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
		 			+ "                                  GROUP BY a.PE_NO_col"
		 			+ "		      Union All"
		 			+ "		      SELECT a.PE_NO_MM AS PE_NO,(SUM(a.afYP)-SUM(b.aFYP)) AS AFYP,(SUM(a.arYP2)-SUM(b.arYP2)) AS ARYP2,0.1*( (SUM(a.afYP)-SUM(b.aFYP))+(SUM(a.arYP2)-SUM(b.arYP2)) ) as PC_AVP"
		 			+ "                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
		 			+ "                                        JOIN TSLI_GA_COMMISSION_NEGATIVE b"
		 			+ "                                        ON a.PE_NO_col = b.PE_NO_col"
		 			+ "                                  WHERE a.pe_no_col = b.pe_no_col"
		 			+ "                                        and a.PE_NO_MM IN (SELECT PE_NO"
		 			+ "                                                          FROM TSLI_GA_AGENT_STRUCTURE "
		 			+ "                                                          WHERE PE_NO_BM = TIER2"
		 			+ "                                                                AND PE_NO_BM = '"+paPeNoBm+"'"
		 			+ "                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
		 			+ "                                        AND b.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
		 			+ "                                  GROUP BY a.PE_NO_MM"
		 			+ "		      Union All"
		 			+ "		      SELECT p.PE_NO_col AS PE_NO,(SUM(p.afYP)-SUM(n.aFYP)) AS AFYP ,(SUM(p.arYP2)-SUM(n.arYP2)) AS ARYP2,( (SUM(p.afYP)-SUM(n.aFYP))+(SUM(p.arYP2)-SUM(n.arYP2)) ) as PC_AVP"
		 			+ "                                  FROM TSLI_GA_COMMISSION_POSITIVE p,TSLI_GA_COMMISSION_NEGATIVE n"
		 			+ "                                  WHERE p.pe_no_col = n.pe_no_col"
		 			+ "                                  and p.DTY_CD='S10240201'"
		 			+ "                                  and p.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
		 			+ "                                  GROUP BY p.PE_NO_col"
		 			+ "		      )  c"
		 			+ "		GROUP BY c.pe_no"
		 			+ "		ORDER BY c.pe_no";

			List<Map<String, Object>> result = db.queryList(vaSql);
   
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		}
	 
	 
public List<Map<String, Object>> getAllGMByYear(String paPeNoBm,String paYear ) {

		 	
	String vaSql = "SELECT  c.pe_no,  SUM(c.afyp)   , SUM(c.aryp2), SUM(c.afyp+c.aryp2) as PC,"
 			+ "		CASE "
 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 0 AND SUM(c.afyp+c.aryp2) <= 2399999 THEN 0.00"
 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 2400000 AND SUM(c.afyp+c.aryp2) <= 4799999 THEN 0.005"
 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 4800000 AND SUM(c.afyp+c.aryp2) <= 7199999 THEN 0.01"
 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 7200000 AND SUM(c.afyp+c.aryp2) <= 9599999 THEN 0.015"
 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 9600000 AND SUM(c.afyp+c.aryp2) <= 11999999 THEN 0.02"
 			+ "		  WHEN SUM(c.afyp+c.aryp2) >= 12000000  THEN 0.025"
 			+ "		  ELSE 0.00"
 			+ "		END AS BONUS,"
 			+ "		CASE"
 			+ "		 WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) >= 0 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) <= 59.99 THEN 0.00"
 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) >= 60 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) <= 74.99 THEN 0.50"
 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) >= 75 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) <= 79.99 THEN 0.90"
 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no= p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) >= 80 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) <= 84.99 THEN 1.00"
 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) >= 85 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) <= 89.99 THEN 1.10"
 			+ "		WHEN (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*) "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) >= 90 AND (SELECT SUM((PRST_BIZRT/SOLI_BIZRT) *100)/COUNT(*)  "
 			+ "		FROM tsli_persistency p  WHERE prst_rte_itm_cd ='S11030B10107' AND c.pe_no = p.pe_no"
 			+ "		AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
 			+ "		) <= 100 THEN 1.20"
 			+ "		 ELSE -1"
 			+ "		END AS PERSISTENCY_CREDIT,"
 			+ "		(SELECT fst_enco_mms FROM tsli_ga_agent_structure a WHERE  c.pe_no = a.pe_no"
 			+ "	AND a.clos_ym  IN ('"+paYear+"12')  ) AS EXPERIENCE ,"
 			+ "		(SELECT credit FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paYear+"12') ) AS CREDIT,"
 			+ "		(SELECT up_level_yn FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paYear+"12') ) AS UP_LEVEL_YN,"
 			+ "		(SELECT reg_dtm FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paYear+"12') ) AS REG_DTM,"
 			+ "		(SELECT pe_no_sm FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paYear+"12') ) AS PE_NO_SM,"
 			+ "		(SELECT pe_no_mm FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paYear+"12') ) AS PE_NO_MM,"
 			+ "		(SELECT pe_no_bm FROM tsli_agent_debt d WHERE d.pe_no_owner = c.pe_no AND d.clos_ym  IN ('"+paYear+"12') ) AS PE_NO_BM"
 			+ "		FROM (SELECT a.PE_NO_MM AS PE_NO, (SUM(a.afYP)-SUM(b.aFYP)) AS AFYP ,(SUM(a.arYP2)-SUM(b.arYP2)) AS ARYP2, ( (SUM(a.afYP)-SUM(b.aFYP))+(SUM(a.arYP2)-SUM(b.arYP2)) ) as PC_AVP"
 			+ "		      FROM TSLI_GA_COMMISSION_POSITIVE a"
 			+ "                                        JOIN TSLI_GA_COMMISSION_NEGATIVE b"
 			+ "                                        ON a.PE_NO_col = b.PE_NO_col"
 			+ "		      WHERE a.PE_NO_MM IN (SELECT PE_NO"
 			+ "		    		  FROM TSLI_GA_AGENT_STRUCTURE "
 			+ "		                                WHERE PE_NO_BM = TIER1"
 			+ "                                                                  AND PE_NO_BM = '"+paPeNoBm+"'"
 			+ "		                                GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD) "
 			+ "                                        AND b.CLOS_YM = a.CLOS_YM"
 			+ "                                        AND TO_DATE(b.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')  "
 			+ "		      GROUP BY a.PE_NO_MM"
 			+ "		      Union All"
 			+ "		      SELECT a.PE_NO_MM AS PE_NO, (SUM(a.afYP)-SUM(b.aFYP)) AS AFYP ,(SUM(a.arYP2)-SUM(b.arYP2)) AS ARYP2, ( (SUM(a.afYP)-SUM(b.aFYP))+(SUM(a.arYP2)-SUM(b.arYP2)) ) as PC_AVP"
 			+ "		      FROM TSLI_GA_COMMISSION_POSITIVE a"
 			+ "                                        JOIN TSLI_GA_COMMISSION_NEGATIVE b"
 			+ "                                        ON a.PE_NO_col = b.PE_NO_col"
 			+ "		      WHERE a.PE_NO_MM IS NULL"
 			+ "                                        AND a.PE_NO_BM = '"+paPeNoBm+"'"
 			+ "                                        AND b.CLOS_YM = a.CLOS_YM"
 			+ "                                        AND TO_DATE(b.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')  "
 			+ "		      GROUP BY a.PE_NO_MM "
 			+ "		      Union All"
 			+ "                                  SELECT a.PE_NO_col AS PE_NO,(SUM(a.afYP)-SUM(b.aFYP)) AS AFYP,(SUM(a.arYP2)-SUM(b.arYP2)) AS ARYP2,0.1*( (SUM(a.afYP)-SUM(b.aFYP))+(SUM(a.arYP2)-SUM(b.arYP2)) ) as PC_AVP"
 			+ "                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
 			+ "                                        JOIN TSLI_GA_COMMISSION_NEGATIVE b"
 			+ "                                        ON a.PE_NO_col = b.PE_NO_col"
 			+ "                                  WHERE a.pe_no_col = b.pe_no_col"
 			+ "                                        and a.PE_NO_col IN (SELECT PE_NO"
 			+ "                                                          FROM TSLI_GA_AGENT_STRUCTURE "
 			+ "                                                          WHERE PE_NO_BM = TIER2"
 			+ "                                                                AND PE_NO_BM = '"+paPeNoBm+"'"
 			+ "                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
 			+ "                                        AND b.CLOS_YM = a.CLOS_YM"
 			+ "                                        AND TO_DATE(b.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')  "
 			+ "                                  GROUP BY a.PE_NO_col"
 			+ "		      Union All"
 			+ "		      SELECT a.PE_NO_MM AS PE_NO,(SUM(a.afYP)-SUM(b.aFYP)) AS AFYP,(SUM(a.arYP2)-SUM(b.arYP2)) AS ARYP2,0.1*( (SUM(a.afYP)-SUM(b.aFYP))+(SUM(a.arYP2)-SUM(b.arYP2)) ) as PC_AVP"
 			+ "                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
 			+ "                                        JOIN TSLI_GA_COMMISSION_NEGATIVE b"
 			+ "                                        ON a.PE_NO_col = b.PE_NO_col"
 			+ "                                  WHERE a.pe_no_col = b.pe_no_col"
 			+ "                                        and a.PE_NO_MM IN (SELECT PE_NO"
 			+ "                                                          FROM TSLI_GA_AGENT_STRUCTURE "
 			+ "                                                          WHERE PE_NO_BM = TIER2"
 			+ "                                                                AND PE_NO_BM = '"+paPeNoBm+"'"
 			+ "                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
 			+ "                                        AND TO_DATE(b.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')  "
 			+ "                                  GROUP BY a.PE_NO_MM"
 			+ "		      Union All"
 			+ "		      SELECT p.PE_NO_col AS PE_NO,(SUM(p.afYP)-SUM(n.aFYP)) AS AFYP ,(SUM(p.arYP2)-SUM(n.arYP2)) AS ARYP2,( (SUM(p.afYP)-SUM(n.aFYP))+(SUM(p.arYP2)-SUM(n.arYP2)) ) as PC_AVP"
 			+ "                                  FROM TSLI_GA_COMMISSION_POSITIVE p,TSLI_GA_COMMISSION_NEGATIVE n"
 			+ "                                  WHERE p.pe_no_col = n.pe_no_col"
 			+ "                                  and p.DTY_CD='S10240201'"
 			+ "                                  AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')  "
 			+ "                                  GROUP BY p.PE_NO_col"
 			+ "		      )  c"
 			+ "		GROUP BY c.pe_no"
 			+ "		ORDER BY c.pe_no";
	
			List<Map<String, Object>> result = db.queryList(vaSql);   
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		}
	 

		public List<Map<String, Object>> getGMResultQuarter(String paPeNoBm,String paQuarterly, String paYear ) {
			 
			 String paMonth1 = "";
			 String paMonth2 = "";
			 String paMonth3 = "";
			 
			 	if(paQuarterly.equals("1")){
			 		paMonth1 = paYear+"01";
			 		paMonth2 = paYear+"02";
			 		paMonth3 = paYear+"03";
			 	}else if(paQuarterly.equals("2")){
			 		paMonth1 = paYear+"04";
			 		paMonth2 = paYear+"05";
			 		paMonth3 = paYear+"06";
			 	}else if(paQuarterly.equals("3")){
			 		paMonth1 = paYear+"07";
			 		paMonth2 = paYear+"08";
			 		paMonth3 = paYear+"09";
			 	}else if(paQuarterly.equals("4")){
			 		paMonth1 = paYear+"10";
			 		paMonth2 = paYear+"11";
			 		paMonth3 = paYear+"12";
			 	}
			 	
			 	String vaSql ="SELECT DISTINCT p.PE_NO_col AS PE_NO,a.NM_TITLE_THAI,a.NM_THAI,a.SURNM_THAI,p.DTY_CD,( (SUM(NVL(p.afYP,0))-SUM(NVL(n.aFYP,0)))+(SUM(NVL(p.arYP2,0))-SUM(NVL(n.arYP2,0))) ) as PC"
			 			+ "	FROM TSLI_GA_COMMISSION_POSITIVE p LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE n ON p.pe_no_col = n.pe_no_col"
			 			+ "                                    JOIN TSLI_GA_AGENT_STRUCTURE a ON a.pe_no = p.pe_no_col"
			 			+ "	WHERE p.DTY_CD ='S10240201'"
			 			+ "       AND p.PE_NO_COL = ?"
			 			+ "       AND p.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')"
			 			+ "	GROUP BY p.PE_NO_col,p.DTY_CD,a.pe_no,a.NM_TITLE_THAI,a.NM_THAI,a.SURNM_THAI";
			 	
			 	
			 	List<Map<String, Object>> result = db.queryList(vaSql,paPeNoBm);   
		        if (result != null && result.size() > 0) {
		            return result;
		        } else {
		            return null;
		        }
			 	
		}
		
		
		public List<Map<String, Object>> getGMResultAnnual(String paPeNoBm, String paYear ) {

			 	String vaSql ="SELECT DISTINCT p.PE_NO_col AS PE_NO,a.NM_TITLE_THAI,a.NM_THAI,a.SURNM_THAI,p.DTY_CD,( (SUM(NVL(p.afYP,0))-SUM(NVL(n.aFYP,0)))+(SUM(NVL(p.arYP2,0))-SUM(NVL(n.arYP2,0))) ) as PC"
			 			+ "	FROM TSLI_GA_COMMISSION_POSITIVE p LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE n ON p.pe_no_col = n.pe_no_col"
			 			+ "                                    JOIN TSLI_GA_AGENT_STRUCTURE a ON a.pe_no = p.pe_no_col"
			 			+ "	WHERE p.DTY_CD ='S10240201'"
			 			+ "       AND p.PE_NO_COL = ?"
			 			+ "       AND TO_DATE(p.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')"
			 			+ "	GROUP BY p.PE_NO_col,p.DTY_CD,a.pe_no,a.NM_TITLE_THAI,a.NM_THAI,a.SURNM_THAI";
			 	
			 	
			 	List<Map<String, Object>> result = db.queryList(vaSql,paPeNoBm);   
		        if (result != null && result.size() > 0) {
		            return result;
		        } else {
		            return null;
		        }
			 	
		}
		
		
		
		public List<Map<String, Object>> getGMResultTier1Quarter(String paPeNoBm,String paQuarterly, String paYear ) {
			 
			 String paMonth1 = "";
			 String paMonth2 = "";
			 String paMonth3 = "";
			 
			 	if(paQuarterly.equals("1")){
			 		paMonth1 = paYear+"01";
			 		paMonth2 = paYear+"02";
			 		paMonth3 = paYear+"03";
			 	}else if(paQuarterly.equals("2")){
			 		paMonth1 = paYear+"04";
			 		paMonth2 = paYear+"05";
			 		paMonth3 = paYear+"06";
			 	}else if(paQuarterly.equals("3")){
			 		paMonth1 = paYear+"07";
			 		paMonth2 = paYear+"08";
			 		paMonth3 = paYear+"09";
			 	}else if(paQuarterly.equals("4")){
			 		paMonth1 = paYear+"10";
			 		paMonth2 = paYear+"11";
			 		paMonth3 = paYear+"12";
			 	}
			 	
			 	String vaSql ="SELECT PE_NO_col AS PE_NO,PC"
			 			+ "                    FROM (SELECT PE_NO_COL, SUM(PC) AS PC"
			 			+ "                         FROM (select PE_NO_MM,PE_NO_COL,DTY_CD,SUM(aFYP)+SUM(arYP2) AS PC"
			 			+ "                             from TSLI_GA_COMMISSION_POSITIVE"
			 			+ "                             where CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')"
			 			+ "                                    AND PE_NO_MM  IN (SELECT PE_NO"
			 			+ "                                            FROM TSLI_GA_AGENT_STRUCTURE "
			 			+ "                                            WHERE PE_NO_BM = TIER1"
			 			+ "                                                   AND PE_NO_BM = ?"
			 			+ "                                            GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD)"
			 			+ "                             group by PE_NO_MM,PE_NO_COL,DTY_CD"
			 			+ "                             UNION ALL"
			 			+ "                             select PE_NO_MM,PE_NO_COL,DTY_CD,-(SUM(aFYP)+SUM(arYP2)) AS PC"
			 			+ "                             from TSLI_GA_COMMISSION_NEGATIVE"
			 			+ "                             where CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			 			+ "                                    AND PE_NO_MM  IN (SELECT PE_NO"
			 			+ "                                            FROM TSLI_GA_AGENT_STRUCTURE "
			 			+ "                                            WHERE PE_NO_BM = TIER1"
			 			+ "                                                   AND PE_NO_BM = ?"
			 			+ "                                            GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD)"
			 			+ "                             group by CLOS_YM,PE_NO_MM,PE_NO_COL,DTY_CD)"
			 			+ "                        GROUP BY PE_NO_COL"
			 			+ "                        Union All"
			 			+ "			 					 				SELECT a.PE_NO_COL,( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			 			+ "			 					 				FROM TSLI_GA_COMMISSION_POSITIVE a LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			 			+ "			 					 				WHERE a.PE_NO_MM IS NULL"
			 			+ "			 					 			        AND a.PE_NO_BM = ?"
			 			+ "			 					 			        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			 			+ "			 					 				GROUP BY a.PE_NO_COL)";
			 	
			 	List<Map<String, Object>> result = db.queryList(vaSql,paPeNoBm,paPeNoBm,paPeNoBm);   
		        if (result != null && result.size() > 0) {
		            return result;
		        } else {
		            return null;
		        }
			 	
		}
		
		
		public List<Map<String, Object>> getGMResultTier1Annual(String paPeNoBm, String paYear ) {
 	
			 	String vaSql ="SELECT PE_NO_col AS PE_NO,PC"
			 			+ "                    FROM (SELECT PE_NO_COL, SUM(PC) AS PC"
			 			+ "                         FROM (select PE_NO_MM,PE_NO_COL,DTY_CD,SUM(aFYP)+SUM(arYP2) AS PC"
			 			+ "                             from TSLI_GA_COMMISSION_POSITIVE"
			 			+ "                             where TO_DATE(CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')"
			 			+ "                                    AND PE_NO_MM  IN (SELECT PE_NO"
			 			+ "                                            FROM TSLI_GA_AGENT_STRUCTURE "
			 			+ "                                            WHERE PE_NO_BM = TIER1"
			 			+ "                                                   AND PE_NO_BM = ?"
			 			+ "                                            GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD)"
			 			+ "                             group by PE_NO_MM,PE_NO_COL,DTY_CD"
			 			+ "                             UNION ALL"
			 			+ "                             select PE_NO_MM,PE_NO_COL,DTY_CD,-(SUM(aFYP)+SUM(arYP2)) AS PC"
			 			+ "                             from TSLI_GA_COMMISSION_NEGATIVE"
			 			+ "                             where TO_DATE(CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
			 			+ "                                    AND PE_NO_MM  IN (SELECT PE_NO"
			 			+ "                                            FROM TSLI_GA_AGENT_STRUCTURE "
			 			+ "                                            WHERE PE_NO_BM = TIER1"
			 			+ "                                                   AND PE_NO_BM = ?"
			 			+ "                                            GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD)"
			 			+ "                             group by CLOS_YM,PE_NO_MM,PE_NO_COL,DTY_CD)"
			 			+ "                        GROUP BY PE_NO_COL"
			 			+ "                        Union All"
			 			+ "			 					 				SELECT a.PE_NO_COL,( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			 			+ "			 					 				FROM TSLI_GA_COMMISSION_POSITIVE a LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			 			+ "			 					 				WHERE a.PE_NO_MM IS NULL"
			 			+ "			 					 			        AND a.PE_NO_BM = ?"
			 			+ "			 					 			        AND TO_DATE(a.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM') "
			 			+ "			 					 				GROUP BY a.PE_NO_COL)";
			 	
			 	
			 	List<Map<String, Object>> result = db.queryList(vaSql,paPeNoBm,paPeNoBm,paPeNoBm);   
		        if (result != null && result.size() > 0) {
		            return result;
		        } else {
		            return null;
		        }
			 	
		}
		
		
		public List<Map<String, Object>> getGMResultTier2Quarter(String paPeNoBm,String paQuarterly, String paYear ) {
			 
			 String paMonth1 = "";
			 String paMonth2 = "";
			 String paMonth3 = "";
			 
			 	if(paQuarterly.equals("1")){
			 		paMonth1 = paYear+"01";
			 		paMonth2 = paYear+"02";
			 		paMonth3 = paYear+"03";
			 	}else if(paQuarterly.equals("2")){
			 		paMonth1 = paYear+"04";
			 		paMonth2 = paYear+"05";
			 		paMonth3 = paYear+"06";
			 	}else if(paQuarterly.equals("3")){
			 		paMonth1 = paYear+"07";
			 		paMonth2 = paYear+"08";
			 		paMonth3 = paYear+"09";
			 	}else if(paQuarterly.equals("4")){
			 		paMonth1 = paYear+"10";
			 		paMonth2 = paYear+"11";
			 		paMonth3 = paYear+"12";
			 	}
			 	
			 	String vaSql ="SELECT DISTINCT *"
			 			+ " FROM(SELECT DISTINCT a.PE_NO_col AS PE_NO, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI,( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			 			+ "		 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			 			+ "		 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			 			+ "		 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			 			+ "                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			 			+ "                                                  ON ag.pe_no = a.PE_NO_col"
			 			+ "		 			                                  WHERE a.PE_NO_col IN (SELECT PE_NO"
			 			+ "		 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			 			+ "		 			                                                          WHERE PE_NO_BM = TIER2"
			 			+ "		 			                                                                AND PE_NO_BM = ?"
			 			+ "		 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			 			+ "		 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
			 			+ "		 			                                 GROUP BY a.PE_NO_col, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI"
			 			+ "		 					      Union All"
			 			+ "		 					         SELECT a.PE_NO_col AS PE_NO, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI,( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			 			+ "		 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			 			+ "		 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			 			+ "		 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			 			+ "                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			 			+ "                                                  ON ag.pe_no = a.PE_NO_col"
			 			+ "		 			                                  WHERE a.PE_NO_MM IN (SELECT PE_NO"
			 			+ "		 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			 			+ "		 			                                                          WHERE PE_NO_BM = TIER2"
			 			+ "		 			                                                                AND PE_NO_BM = ?"
			 			+ "		 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			 			+ "		 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
			 			+ "		 			                                 GROUP BY a.PE_NO_col, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI)";
			 	
			 	
			 	List<Map<String, Object>> result = db.queryList(vaSql,paPeNoBm,paPeNoBm);   
		        if (result != null && result.size() > 0) {
		            return result;
		        } else {
		            return null;
		        }
			 	
		}
		
		
		public List<Map<String, Object>> getGMResultTier2Annual(String paPeNoBm, String paYear ) {
	
			String vaSql ="SELECT DISTINCT *"
			 			+ " FROM(SELECT a.PE_NO_col AS PE_NO, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI,( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
		 			+ "		 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
		 			+ "		 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
		 			+ "		 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
		 			+ "                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
		 			+ "                                                  ON ag.pe_no = a.PE_NO_col"
		 			+ "		 			                                  WHERE a.PE_NO_col IN (SELECT PE_NO"
		 			+ "		 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
		 			+ "		 			                                                          WHERE PE_NO_BM = TIER2"
		 			+ "		 			                                                                AND PE_NO_BM = ?"
		 			+ "		 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
		 			+ "		 			                                        AND TO_DATE(a.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')  "
		 			+ "		 			                                 GROUP BY a.PE_NO_col, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI"
		 			+ "		 					      Union All"
		 			+ "		 					         SELECT a.PE_NO_col AS PE_NO, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI,( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
		 			+ "		 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
		 			+ "		 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
		 			+ "		 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
		 			+ "                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
		 			+ "                                                  ON ag.pe_no = a.PE_NO_col"
		 			+ "		 			                                  WHERE a.PE_NO_MM IN (SELECT PE_NO"
		 			+ "		 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
		 			+ "		 			                                                          WHERE PE_NO_BM = TIER2"
		 			+ "		 			                                                                AND PE_NO_BM = ?"
		 			+ "		 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
		 			+ "		 			                                        AND TO_DATE(a.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')  "
		 			+ "		 			                                 GROUP BY a.PE_NO_col, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI)";
			 	
			 	
			 	List<Map<String, Object>> result = db.queryList(vaSql,paPeNoBm,paPeNoBm);   
		        if (result != null && result.size() > 0) {
		            return result;
		        } else {
		            return null;
		        }
			 	
		}
/** 
 * Find agent All 
 * @param vaCloseYm
 * @return
 */
public List<Map<String, Object>> countAgent(String vaCloseYm) {
	 List<Map<String , Object>> result = null;
	 try { 
	
		 	String vaSql = "																													\n"+
		 			" SELECT PE_NO_SM ,COUNT(PE_NO_COL) AS COUNT_PE_NO                                                                          \n"+
		 			" FROM                                                                                                                      \n"+
		 			"                                                                                                                           \n"+
		 			" (                                                                                                                         \n"+
		 			" SELECT PE_NO_SM                                                                                                           \n"+
		 			" ,PE_NO_COL                                                                                                                \n"+
		 			" ,SUM(COUNT_PERSON) AS COUNT_PERSON                                                                                        \n"+
		 			" FROM (                                                                                                                    \n"+
		 			"                                                                                                                           \n"+
		 			"                                                                                                                           \n"+
		 			" SELECT '" + vaCloseYm +"' as YYYYMM , PAYMENT_MODE , PE_NO_SM,pe_no_col, COUNT(0) AS COUNT_PERSON                         \n"+
		 			" FROM                                                                                                                      \n"+
		 			" (  SELECT  a.pe_no_sm,a.pe_no_col,a.po_no,  1*(SUM(a.FYP)) AS SUM_FYP ,  SUBSTR(a.PAY_MOD_CD,7,2) as payment_mode         \n"+
		 			" FROM tsli_ga_commission_positive  a  LEFT JOIN                                                                            \n"+
		 			" (  SELECT pe_no                                                                                                           \n"+
		 			" FROM tsli_ga_agent_structure                                                                                              \n"+
		 			" WHERE  LCNS_RNWL_YMD IS NOT NULL   ) d ON a.pe_no_col = d.pe_no                                                           \n"+
		 			" WHERE SUBSTR(a.PAY_MOD_CD, 7 , 2) = '12'  AND a.CONT_MMS = 1                                                              \n"+
		 			" AND a.CLOS_YM ='" + vaCloseYm +"'  AND a.COVER_PRD <> 1  AND a.PAY_PRD <> 1                                               \n"+
		 			"  AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 																\n"+
		 			" GROUP BY a.pe_no_sm , a.pe_no_col , a.po_no , SUBSTR(a.PAY_MOD_CD,7,2)                                                    \n"+
		 			" HAVING 1*(SUM(a.FYP)) >= 7000  )                                                                                          \n"+
		 			" GROUP BY pe_no_sm,pe_no_col , payment_mode                                                                                \n"+
		 			"                                                                                                                           \n"+
		 			"                                                                                                                           \n"+
		 			" UNION ALL                                                                                                                 \n"+
		 			"                                                                                                                           \n"+
		 			" SELECT  '" + vaCloseYm +"' as YYYYMM , payment_mode ,  pe_no_sm,pe_no_col,count(0) AS COUNT_PERSION                       \n"+
		 			" from (  SELECT a.pe_no_sm,a.pe_no_col,a.po_no,  2*(SUM(a.FYP)) AS SUM_FYP,  SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode 	\n"+
		 			" FROM tsli_ga_commission_positive  a  LEFT JOIN                                                                            \n"+
		 			" (  SELECT pe_no                                                                                                           \n"+
		 			" FROM tsli_ga_agent_structure  WHERE  LCNS_RNWL_YMD IS NOT NULL  )  d  ON a.pe_no_col = d.pe_no                            \n"+
		 			" WHERE SUBSTR(a.PAY_MOD_CD, 7 , 2) ='06'  and a.CONT_MMS = 1  and a.CLOS_YM ='" + vaCloseYm +"'                            \n"+
		 			"  AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 																\n"+
		 			" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                                                     \n"+
		 			" Having 2*(SUM(a.FYP)) >=12000  ) A                                                                                        \n"+
		 			" GROUP BY A.pe_no_sm,pe_no_col , payment_mode                                                                              \n"+
		 			"                                                                                                                           \n"+
		 			" UNION ALL                                                                                                                 \n"+
		 			"                                                                                                                           \n"+
		 			" SELECT  '" + vaCloseYm +"' as YYYYMM , payment_mode ,  pe_no_sm, pe_no_col,count(0) AS COUNT_PERSION                      \n"+
		 			" from (  select a.pe_no_sm,a.pe_no_col,a.po_no,  4*(SUM(a.FYP)) AS SUM_FYP,  SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode   \n"+
		 			" from tsli_ga_commission_positive  a  left join                                                                            \n"+
		 			" (  SELECT pe_no                                                                                                           \n"+
		 			" FROM tsli_ga_agent_structure                                                                                              \n"+
		 			" WHERE  LCNS_RNWL_YMD IS NOT NULL  )  d  on a.pe_no_col = d.pe_no                                                          \n"+
		 			" WHERE SUBSTR(a.PAY_MOD_CD, 7 ,2) ='03'  AND a.CONT_MMS = 1  AND a.CLOS_YM ='" + vaCloseYm +"'                             \n"+
		 			"  AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 																\n"+
		 			" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                                                     \n"+
		 			" Having 4*(SUM(a.FYP))  >=12000  ) A                                                                                       \n"+
		 			" GROUP BY A.pe_no_sm,pe_no_col , payment_mode                                                                              \n"+
		 			"                                                                                                                           \n"+
		 			" UNION ALL                                                                                                                 \n"+
		 			"                                                                                                                           \n"+
		 			" SELECT  '" + vaCloseYm +"' as YYYYMM , payment_mode ,  pe_no_sm , pe_no_col, ( 0.5 * count(0) ) as COUNT_PERSION          \n"+
		 			" from (  select a.pe_no_sm,a.pe_no_col,a.po_no,  12*(SUM(a.FYP)) AS SUM_FYP,  SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode  \n"+
		 			" from tsli_ga_commission_positive  a  left join                                                                            \n"+
		 			" (  SELECT pe_no                                                                                                           \n"+
		 			" FROM tsli_ga_agent_structure                                                                                              \n"+
		 			" WHERE  LCNS_RNWL_YMD is not null  ) d  on a.pe_no_col = d.pe_no                                                           \n"+
		 			" where  SUBSTR(a.PAY_MOD_CD, 7 , 2) = '01'  and a.CONT_MMS = 1                                                             \n"+
		 			" and a.CLOS_YM ='" + vaCloseYm +"'                                                                                         \n"+
		 			"  AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 																\n"+
		 			" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                                                     \n"+
		 			" Having 12*(SUM(a.FYP)) >=12000  )  A                                                                                      \n"+
		 			" GROUP BY A.pe_no_sm,a.pe_no_col , payment_mode                                                                            \n"+
		 			"                                                                                                                           \n"+
		 			" )                                                                                                                         \n"+
		 			" GROUP BY PE_NO_SM,PE_NO_COL                                                                                               \n"+
		 			" HAVING SUM(COUNT_PERSON)>=1                                                                                               \n"+
		 			" )                                                                                                                         \n"+
		 			" GROUP BY PE_NO_SM                                                                                                         \n";


//		  	System.out.println("countAgent : " + vaSql);
		 	result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		 
	 } catch (Exception ex){ 
		 ex.printStackTrace();
	 }
	
	 return result;
}



public List<Map<String, Object>> countAgentForReCal(String vaCloseYm,String currentYYYYMM) {
	 List<Map<String , Object>> result = null;
	 try { 
		 

		 
	
		 	String vaSql = "																													\n"+
		 			" SELECT PE_NO_SM ,COUNT(PE_NO_COL) AS COUNT_PE_NO                                                                          \n"+
		 			" FROM                                                                                                                      \n"+
		 			"                                                                                                                           \n"+
		 			" (                                                                                                                         \n"+
		 			" SELECT PE_NO_SM                                                                                                           \n"+
		 			" ,PE_NO_COL                                                                                                                \n"+
		 			" ,SUM(COUNT_PERSON) AS COUNT_PERSON                                                                                        \n"+
		 			" FROM (                                                                                                                    \n"+
		 			"                                                                                                                           \n"+
		 			"                                                                                                                           \n"+
		 			" SELECT '" + vaCloseYm +"' as YYYYMM , PAYMENT_MODE , PE_NO_SM,pe_no_col, COUNT(0) AS COUNT_PERSON                         \n"+
		 			" FROM                                                                                                                      \n"+
		 			" (  SELECT  a.pe_no_sm,a.pe_no_col,a.po_no,  1*(SUM(a.FYP)) AS SUM_FYP ,  SUBSTR(a.PAY_MOD_CD,7,2) as payment_mode         \n"+
		 			" FROM tsli_ga_commission_positive  a  LEFT JOIN                                                                            \n"+
		 			" (  SELECT pe_no                                                                                                           \n"+
		 			" FROM tsli_ga_agent_structure                                                                                              \n"+
		 			" WHERE  LCNS_RNWL_YMD IS NOT NULL   ) d ON a.pe_no_col = d.pe_no                                                           \n"+
		 			" WHERE SUBSTR(a.PAY_MOD_CD, 7 , 2) = '12'  AND a.CONT_MMS = 1                                                              \n"+
		 			" AND a.CLOS_YM ='" + vaCloseYm +"'  AND a.COVER_PRD <> 1  AND a.PAY_PRD <> 1                                               \n"+
		 			"  AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 																\n"+
		 			"	AND (a.CLOS_YM,a.PO_NO,a.PE_NO_COL,a.RIDER_CD) NOT IN		     														\n"+
		 			"	(                                                                														\n"+
		 			"	select PO_CLOS_YM as CLOS_YM,PO_NO,PE_NO_COL,RIDER_CD																	\n"+			
		 			"	from TSLI_TEMP_AL_FREELOOK																								\n"+
		 			"	)                                                                														\n"+
		 			" GROUP BY a.pe_no_sm , a.pe_no_col , a.po_no , SUBSTR(a.PAY_MOD_CD,7,2)                                                    \n"+
		 			" HAVING 1*(SUM(a.FYP)) >= 7000  )                                                                                          \n"+
		 			" GROUP BY pe_no_sm,pe_no_col , payment_mode                                                                                \n"+
		 			"                                                                                                                           \n"+
		 			"                                                                                                                           \n"+
		 			" UNION ALL                                                                                                                 \n"+
		 			"                                                                                                                           \n"+
		 			" SELECT  '" + vaCloseYm +"' as YYYYMM , payment_mode ,  pe_no_sm,pe_no_col,count(0) AS COUNT_PERSION                       \n"+
		 			" from (  SELECT a.pe_no_sm,a.pe_no_col,a.po_no,  2*(SUM(a.FYP)) AS SUM_FYP,  SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode 	\n"+
		 			" FROM tsli_ga_commission_positive  a  LEFT JOIN                                                                            \n"+
		 			" (  SELECT pe_no                                                                                                           \n"+
		 			" FROM tsli_ga_agent_structure  WHERE  LCNS_RNWL_YMD IS NOT NULL  )  d  ON a.pe_no_col = d.pe_no                            \n"+
		 			" WHERE SUBSTR(a.PAY_MOD_CD, 7 , 2) ='06'  and a.CONT_MMS = 1  and a.CLOS_YM ='" + vaCloseYm +"'                            \n"+
		 			"  AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 																\n"+
		 			"	AND (a.CLOS_YM,a.PO_NO,a.PE_NO_COL,a.RIDER_CD) NOT IN		     														\n"+
		 			"	(                                                                														\n"+
		 			"	select PO_CLOS_YM as CLOS_YM,PO_NO,PE_NO_COL,RIDER_CD																	\n"+			
		 			"	from TSLI_TEMP_AL_FREELOOK																								\n"+
		 			"	)                                                                														\n"+
		 			" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                                                     \n"+
		 			" Having 2*(SUM(a.FYP)) >=12000  ) A                                                                                        \n"+
		 			" GROUP BY A.pe_no_sm,pe_no_col , payment_mode                                                                              \n"+
		 			"                                                                                                                           \n"+
		 			" UNION ALL                                                                                                                 \n"+
		 			"                                                                                                                           \n"+
		 			" SELECT  '" + vaCloseYm +"' as YYYYMM , payment_mode ,  pe_no_sm, pe_no_col,count(0) AS COUNT_PERSION                      \n"+
		 			" from (  select a.pe_no_sm,a.pe_no_col,a.po_no,  4*(SUM(a.FYP)) AS SUM_FYP,  SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode   \n"+
		 			" from tsli_ga_commission_positive  a  left join                                                                            \n"+
		 			" (  SELECT pe_no                                                                                                           \n"+
		 			" FROM tsli_ga_agent_structure                                                                                              \n"+
		 			" WHERE  LCNS_RNWL_YMD IS NOT NULL  )  d  on a.pe_no_col = d.pe_no                                                          \n"+
		 			" WHERE SUBSTR(a.PAY_MOD_CD, 7 ,2) ='03'  AND a.CONT_MMS = 1  AND a.CLOS_YM ='" + vaCloseYm +"'                             \n"+
		 			"  AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 																\n"+
		 			"	AND (a.CLOS_YM,a.PO_NO,a.PE_NO_COL,a.RIDER_CD) NOT IN		     														\n"+
		 			"	(                                                               			 											\n"+
		 			"	select PO_CLOS_YM as CLOS_YM,PO_NO,PE_NO_COL,RIDER_CD																	\n"+			
		 			"	from TSLI_TEMP_AL_FREELOOK																								\n"+
		 			"	)                                                                														\n"+
		 			" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                                                     \n"+
		 			" Having 4*(SUM(a.FYP))  >=12000  ) A                                                                                       \n"+
		 			" GROUP BY A.pe_no_sm,pe_no_col , payment_mode                                                                              \n"+
		 			"                                                                                                                           \n"+
		 			" UNION ALL                                                                                                                 \n"+
		 			"                                                                                                                           \n"+
		 			" SELECT  '" + vaCloseYm +"' as YYYYMM , payment_mode ,  pe_no_sm , pe_no_col, ( 0.5 * count(0) ) as COUNT_PERSION          \n"+
		 			" from (  select a.pe_no_sm,a.pe_no_col,a.po_no,  12*(SUM(a.FYP)) AS SUM_FYP,  SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode  \n"+
		 			" from tsli_ga_commission_positive  a  left join                                                                            \n"+
		 			" (  SELECT pe_no                                                                                                           \n"+
		 			" FROM tsli_ga_agent_structure                                                                                              \n"+
		 			" WHERE  LCNS_RNWL_YMD is not null  ) d  on a.pe_no_col = d.pe_no                                                           \n"+
		 			" where  SUBSTR(a.PAY_MOD_CD, 7 , 2) = '01'  and a.CONT_MMS = 1                                                             \n"+
		 			" and a.CLOS_YM ='" + vaCloseYm +"'                                                                                         \n"+
		 			"  AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 																\n"+
		 			"	AND (a.CLOS_YM,a.PO_NO,a.PE_NO_COL,a.RIDER_CD) NOT IN		     														\n"+
		 			"	(                                                                														\n"+
		 			"	select PO_CLOS_YM as CLOS_YM,PO_NO,PE_NO_COL,RIDER_CD																	\n"+			
		 			"	from TSLI_TEMP_AL_FREELOOK																								\n"+
		 			"	)                                                                														\n"+
		 			" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                                                     \n"+
		 			" Having 12*(SUM(a.FYP)) >=12000  )  A                                                                                      \n"+
		 			" GROUP BY A.pe_no_sm,a.pe_no_col , payment_mode                                                                            \n"+
		 			"                                                                                                                           \n"+
		 			" )                                                                                                                         \n"+
		 			" GROUP BY PE_NO_SM,PE_NO_COL                                                                                               \n"+
		 			" HAVING SUM(COUNT_PERSON)>=1                                                                                               \n"+
		 			" )                                                                                                                         \n"+
		 			" GROUP BY PE_NO_SM                                                                                                         \n";


//		  	System.out.println("countAgentForReCal \n : " + vaSql);
		 	result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		 
	 } catch (Exception ex){ 
		 ex.printStackTrace();
	 }
	
	 return result;
}

public String getFreeLookMinClosYYYYMM(String vaCloseYm){
	
	String vaSql = ""+
	"	SELECT MIN(p.CLOS_YM) as MIN_CLOS_YM                     	"+  
	"	FROM tsli_ga_commission_negative n                          "+        
	"	LEFT JOIN tsli_ga_commission_positive p                     "+        
	"	ON n.po_no =  p.po_no                                       "+    
	"	WHERE                                                       "+         
	"	n.PE_NO_SM in                                               "+        
	"	(SELECT PE_NO                                               "+         
	"	FROM tsli_ga_agent_structure                                "+        
	"	WHERE dty_cd ='S10240203' and LCNS_RNWL_YMD IS NOT NULL)    "+                            
	"	AND n.PO_NO  NOT IN                                         "+        
	"	(SELECT a.PO_NO                                             "+         
	"	FROM tsli_ga_commission_positive a                          "+        
	"	WHERE a.clos_ym >= '"+vaCloseYm+"')                         ";
	
	Map<String, Object> result = db.querySingle(vaSql);
	  
	if (result.get("MIN_CLOS_YM") != null && result.size() > 0) {
		return result.get("MIN_CLOS_YM").toString();
	} else {
		return null;
	}	
}



public List<Map<String, Object>> getFreeLooks(String vaCloseYm){
	
	List<Map<String , Object>> result = null;
	
	try{
		
	String vaSql = ""+
	"	SELECT p.CLOS_YM as P_CLOS_YM,p.PE_NO_COL										"+	 
	"	,p.PO_NO,SUBSTR(p.PAY_MOD_CD,7,2) AS PO_TYPE,( p.AFYP + p.ARYP2 ) as PC_AL		"+     
	"	FROM tsli_ga_commission_negative n                                  			"+     
	"	LEFT JOIN tsli_ga_commission_positive p                             			"+     
	"		ON n.po_no =  p.po_no                                           			"+     
	"	WHERE                                                               			"+       
	"	n.PE_NO_SM in                                                       			"+     
	"	(                                                                   			"+     
	"	SELECT PE_NO                                                        			"+     
	"	FROM tsli_ga_agent_structure                                        			"+     
	"	WHERE dty_cd ='S10240203' and LCNS_RNWL_YMD IS NOT NULL             			"+     
	"	)                                                                   			"+       
	"	AND n.PO_NO  NOT IN                                                 			"+     
	"	(                                                                   			"+     
	"	SELECT a.PO_NO                                                      			"+     
	"	FROM tsli_ga_commission_positive a                                  			"+     
	"	WHERE a.clos_ym >= '"+vaCloseYm+"'                                  			"+     
	"	)                                                                   			"+
	"	ORDER BY P_CLOS_YM																";   
	
		result = db.queryList(vaSql);
  
		if (result != null && result.size() > 0) {
			return result;
		} else {
			return null;
		}
		} catch (Exception ex){ 
			ex.printStackTrace();
		}
	
	return result;
}

public List<Map<String, Object>> getAllPcByQuarterly(String paPeno,String paMonth1,String paMonth2,String paMonth3, String paPersistencyCategory ) {
	
	
	String vaSql = "SELECT pe_no_col,SUM(PC) AS PC FROM (SELECT pe_no_col,afyp+aryp2 AS PC FROM tsli_ga_commission_positive p "
			+ "WHERE  dty_cd  IN ('S10240205','S10240206') "
			+ "AND clos_ym IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			+ "AND (COALESCE(pe_no_col, ' ') LIKE '"+paPeno+"') "
			+ "UNION ALL "
			+ "SELECT pe_no_col,-(afyp+aryp2) AS PC FROM tsli_ga_commission_negative p "
			+ "WHERE  dty_cd  IN ('S10240205','S10240206') "
			+ "AND clos_ym IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			+ "AND  (COALESCE(pe_no_col, ' ') LIKE '"+paPeno+"')) GROUP BY pe_no_col ORDER BY pe_no_col";
	
	List<Map<String, Object>> result = db.queryList(vaSql);
    if (result != null && result.size() > 0) {
        return result;
    } else {
        return null;
    }
}

public List<Map<String, Object>> getAllPcByAnnual(String paPeno,String paYear, String paPersistencyCategory ) {
	
	
	String vaSql = "SELECT pe_no_col,SUM(PC) AS PC FROM (SELECT pe_no_col,afyp+aryp2 AS PC FROM tsli_ga_commission_positive p "
			+ "WHERE  dty_cd  IN ('S10240205','S10240206') "
			+ "AND clos_ym IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11') "
			+ "AND (COALESCE(pe_no_col, ' ') LIKE '"+paPeno+"') "
			+ "UNION ALL "
			+ "SELECT pe_no_col,-(afyp+aryp2) AS PC FROM tsli_ga_commission_negative p "
			+ "WHERE  dty_cd  IN ('S10240205','S10240206') "
			+ "AND clos_ym IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11') "
			+ "AND  (COALESCE(pe_no_col, ' ') LIKE '"+paPeno+"')) GROUP BY pe_no_col ORDER BY pe_no_col";
	
	List<Map<String, Object>> result = db.queryList(vaSql);
    if (result != null && result.size() > 0) {
        return result;
    } else {
        return null;
    }
}

public Map<String, Object> getPersistencyCreditByQuarterly(String paPeNo,String paMonth3, String paPersistencyCategory ) {
	String vaSql = "SELECT pe_no,prst_rte FROM tsli_persistency   WHERE prst_rte_itm_cd ='"+paPersistencyCategory+"' "
			+ "AND  pe_no = '"+paPeNo+"' AND clos_ym  IN('"+paMonth3+"')";
	Map<String, Object> result = db.querySingle(vaSql);
    if (result != null && result.size() > 0) {
        return result;
    } else {
        return null;
    }
}
public Map<String, Object> getPersistencyCreditByAnnual(String paPeNo,String paYear, String paPersistencyCategory ) {
	String vaSql = "SELECT pe_no,prst_rte FROM tsli_persistency   WHERE prst_rte_itm_cd ='"+paPersistencyCategory+"' "
			+ "AND  pe_no = '"+paPeNo+"' AND clos_ym  IN ('"+paYear+"11')";
	Map<String, Object> result = db.querySingle(vaSql);
    if (result != null && result.size() > 0) {
        return result;
    } else {
        return null;
    }
}
public Map<String, Object> getExperienceByQuarterly(String paPeNo,String paMonth3 ) {
	String vaSql = "SELECT pe_no,fst_enco_mms FROM tsli_ga_agent_structure  WHERE "
			+ "pe_no = '"+paPeNo+"' AND clos_ym  IN ('"+paMonth3+"')";
	
	Map<String, Object> result = db.querySingle(vaSql);;
    if (result != null && result.size() > 0) {
        return result;
    } else {
        return null;
    }
}
public Map<String, Object> getExperienceByAnnual(String paPeNo,String paYear ) {
	String vaSql = "SELECT pe_no,fst_enco_mms FROM tsli_ga_agent_structure  WHERE "
			+ "pe_no = '"+paPeNo+"' AND clos_ym  IN ('"+paYear+"11')";
	
	Map<String, Object> result = db.querySingle(vaSql);;
    if (result != null && result.size() > 0) {
        return result;
    } else {
        return null;
    }
}
public Map<String, Object> getCreditByQuarterly(String paPeNo,String paMonth3 ) {
	String vaSql = "SELECT pe_no_owner,credit FROM tsli_agent_debt WHERE pe_no_owner = '"+paPeNo+"' AND clos_ym  IN ('"+paMonth3+"')";
	
	Map<String, Object> result = db.querySingle(vaSql);;
    if (result != null && result.size() > 0) {
        return result;
    } else {
        return null;
    }
}
public Map<String, Object> getCreditByAnnual(String paPeNo,String paYear ) {
	String vaSql = "SELECT pe_no_owner,credit FROM tsli_agent_debt WHERE pe_no_owner = '"+paPeNo+"' AND clos_ym  IN ('"+paYear+"11')";
	
	Map<String, Object> result = db.querySingle(vaSql);;
    if (result != null && result.size() > 0) {
        return result;
    } else {
        return null;
    }
}

public String findNameById(String paPeNo) {
	 String vaSql = "SELECT nm_title_thai, nm_thai, surnm_thai FROM tsli_ga_agent_structure WHERE pe_no = ?";
	 Map<String, Object> result = db.querySingle(vaSql, paPeNo);
	 String vaResult = "";
	 if (result != null) {
		 vaResult = (String) result.get("NM_TITLE_THAI")+" "+(String) result.get("NM_THAI")+" "+(String) result.get("SURNM_THAI");
		 return vaResult;
	 } else {
		 vaResult = "";
		
		 return vaResult;
    }
}

public String findDtyCDById(String paPeNo) {
	 String vaSql = "SELECT DTY_CD FROM tsli_ga_agent_structure WHERE pe_no = ?";
	 Map<String, Object> result = db.querySingle(vaSql, paPeNo);
	 String vaResult = "";
	 if (result != null) {
		 vaResult = result.get("DTY_CD").toString();
		 return vaResult;
	 } else {
		 vaResult = "";
		
		 return vaResult;
   }
}

public List<Map<String, Object>> getActiveAgAndAlList(String vaPeNoSm,String vaCloseYm) {
	
	String vaSql = ""+
			"	SELECT CLOS_YM,pe_no_col, po_no , payment_mode                                                         \n"+                                                 
			" 	,SUM(AMOUNT) AS AMOUNT                                                                                 \n"+       
			" 	FROM (                                                                                                 \n"+
			"	--PAY_MOD_CD = 12																					   \n"+
			"	SELECT  SUBSTR(CLOS_YM,5,2)||'/'||SUBSTR(CLOS_YM,1,4) as CLOS_YM , pe_no_col , po_no , PAYMENT_MODE    \n"+
			"	, COUNT(0) AS AMOUNT                                                                                   \n"+
			"	FROM                                                                                                   \n"+                   
			"	(                                                                                                      \n"+
			"	SELECT  a.CLOS_YM,a.pe_no_sm,a.pe_no_col,a.po_no,  1*(SUM(a.FYP)) AS SUM_FYP ,                         \n"+
			"	SUBSTR(a.PAY_MOD_CD,7,2) as payment_mode                                                               \n"+
			"	FROM tsli_ga_commission_positive  a  LEFT JOIN                                                         \n"+                   
			"	(  SELECT pe_no                                                                                        \n"+                   
			"	FROM tsli_ga_agent_structure                                                                           \n"+                   
			"	WHERE  LCNS_RNWL_YMD IS NOT NULL   ) d ON a.pe_no_col = d.pe_no                                        \n"+                   
			"	WHERE SUBSTR(a.PAY_MOD_CD, 7 , 2) = '12'  AND a.CONT_MMS = 1                                           \n"+                   
			"	AND a.CLOS_YM = " + vaCloseYm +" --clos_ym                                                             \n"+
			"	AND a.pe_no_sm = " + vaPeNoSm +" -- pe_no_sm                                                           \n"+
			"	AND a.COVER_PRD <> 1  AND a.PAY_PRD <> 1                                                               \n"+
			"	AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' )	                                           \n"+
			"	GROUP BY a.CLOS_YM,a.pe_no_sm , a.pe_no_col , a.po_no , SUBSTR(a.PAY_MOD_CD,7,2)                       \n"+                             
			"	HAVING 1*(SUM(a.FYP)) >= 7000                                                                          \n"+
			"	)                                                                                                      \n"+
			"	GROUP BY CLOS_YM,pe_no_col, po_no , payment_mode                                                       \n"+
			"	                                                                                                       \n"+
			"	UNION ALL                                                                                              \n"+
			"	                                                                                                       \n"+
			"	--PAY_MOD_CD = 06                                                                                      \n"+
			"	SELECT  SUBSTR(CLOS_YM,5,2)||'/'||SUBSTR(CLOS_YM,1,4) as CLOS_YM , pe_no_col , po_no , PAYMENT_MODE    \n"+
			"	, COUNT(0) AS AMOUNT                                                                                   \n"+
			"	from (  SELECT a.CLOS_YM, a.pe_no_sm,a.pe_no_col,a.po_no,  2*(SUM(a.FYP)) AS SUM_FYP,                  \n"+
			"	SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode 	                                                       \n"+
			"	FROM tsli_ga_commission_positive  a  LEFT JOIN                                                         \n"+                   
			"	(  SELECT pe_no                                                                                        \n"+                   
			"	FROM tsli_ga_agent_structure  WHERE  LCNS_RNWL_YMD IS NOT NULL  )  d  ON a.pe_no_col = d.pe_no         \n"+                   
			"	WHERE SUBSTR(a.PAY_MOD_CD, 7 , 2) ='06'  and a.CONT_MMS = 1                                            \n"+
			"	and a.CLOS_YM = " + vaCloseYm +" -- clos_ym                                                            \n"+
			"	AND a.pe_no_sm = '" + vaPeNoSm +"' -- pe_no_sm                                                         \n"+
			"	AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 												\n"+				
			"	GROUP BY a.CLOS_YM,a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                        \n"+                             
			"	Having 2*(SUM(a.FYP)) >=12000  )                                                                       \n"+                 
			"	GROUP BY CLOS_YM,pe_no_col, po_no , payment_mode                                                       \n"+
			"	                                                                                                       \n"+
			"	UNION ALL                                                                                              \n"+
			"	                                                                                                       \n"+
			"	--PAY_MOD_CD = 03                                                                                      \n"+
			"	SELECT  SUBSTR(CLOS_YM,5,2)||'/'||SUBSTR(CLOS_YM,1,4) as CLOS_YM , pe_no_col , po_no , PAYMENT_MODE    \n"+
			"	, COUNT(0) AS AMOUNT                                                                                   \n"+
			"	from (  select a.clos_ym,a.pe_no_sm,a.pe_no_col,a.po_no,  4*(SUM(a.FYP)) AS SUM_FYP,                   \n"+
			"	SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode                                                            \n"+
			"	from tsli_ga_commission_positive  a  left join                                                         \n"+                   
			"	(  SELECT pe_no                                                                                        \n"+                   
			"	FROM tsli_ga_agent_structure                                                                           \n"+                   
			"	WHERE  LCNS_RNWL_YMD IS NOT NULL  )  d  on a.pe_no_col = d.pe_no                                       \n"+                   
			"	WHERE SUBSTR(a.PAY_MOD_CD, 7 ,2) ='03'  AND a.CONT_MMS = 1                                             \n"+
			"	and a.CLOS_YM = " + vaCloseYm +" -- clos_ym                                                             \n"+
			"	AND a.pe_no_sm = " + vaPeNoSm +" -- pe_no_sm                                                            \n"+
			"	AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 												\n"+				
			"	GROUP BY a.clos_ym,a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                        \n"+                             
			"	Having 4*(SUM(a.FYP))  >=12000  )                                                                      \n"+                  
			"	GROUP BY CLOS_YM,pe_no_col, po_no , payment_mode                                                       \n"+
			"	                                                                                                       \n"+
			"	UNION ALL                                                                                              \n"+
			"	                                                                                                       \n"+
			"	--PAY_MOD_CD = 01                                                                                      \n"+
			"	SELECT  SUBSTR(CLOS_YM,5,2)||'/'||SUBSTR(CLOS_YM,1,4) as CLOS_YM , pe_no_col , po_no , PAYMENT_MODE    \n"+
			"	, ( 0.5 * count(0) ) AS amount                                                                         \n"+
			"	from (  select a.clos_ym,a.pe_no_sm,a.pe_no_col,a.po_no,  12*(SUM(a.FYP)) AS SUM_FYP,                  \n"+
			"	SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode                                                            \n"+
			"	from tsli_ga_commission_positive  a  left join                                                         \n"+                   
			"	(  SELECT pe_no                                                                                        \n"+                   
			"	FROM tsli_ga_agent_structure                                                                           \n"+                   
			"	WHERE  LCNS_RNWL_YMD is not null  ) d  on a.pe_no_col = d.pe_no                                        \n"+                   
			"	where  SUBSTR(a.PAY_MOD_CD, 7 , 2) = '01'  and a.CONT_MMS = 1                                          \n"+   
			"	and a.CLOS_YM = " + vaCloseYm +"   --clos_ym                                                           \n"+     
			"	AND a.pe_no_sm = " + vaPeNoSm +" -- pe_no_sm                                                           \n"+     
			"	AND a.DTY_CD in ( 'S10240205', 'S10240206','S10240203' ) 												\n"+				
			"	GROUP BY a.clos_ym,a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2)                        \n"+                             
			"	Having 12*(SUM(a.FYP)) >=12000  )                                                                      \n"+                  
			"	GROUP BY CLOS_YM,pe_no_col, po_no , payment_mode                                                       \n"+
	        "                                                                                                          \n"+
			"	)                                                                                                      \n"+                   
			" 	GROUP BY CLOS_YM,pe_no_col, po_no , payment_mode                                                       \n"+                                       
			" 	HAVING SUM(amount)>=1                                                                                  \n";
	
	List<Map<String, Object>> result = db.queryList(vaSql);
    	if (result != null && result.size() > 0) {
        	return result;
    	} else {
    		return null;
    	}	 
	}
public List<Map<String, Object>> getPCByQuarterly(String vaPeNo, String paMonth1, String paMonth2,String paMonth3) {

	 
	String vaSql = "SELECT clos_ym,pe_no_col,po_no,afyp ,aryp2 FROM "
			+ "(SELECT clos_ym,pe_no_col,po_no,afyp,aryp2 FROM tsli_ga_commission_positive p "
			+ "WHERE  dty_cd  IN ('S10240205','S10240206') AND clos_ym IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			+ "AND (COALESCE(pe_no_col, ' ') LIKE '"+vaPeNo+"') "
			+ "UNION ALL "
			+ "SELECT clos_ym,pe_no_col,po_no,-(afyp) AS afyp ,-(TO_NUMBER(aryp2)) AS aryp2  FROM tsli_ga_commission_negative p "
			+ "WHERE  dty_cd  IN ('S10240205','S10240206') AND clos_ym IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			+ "AND  (COALESCE(pe_no_col, ' ') LIKE '"+vaPeNo+"')) GROUP BY clos_ym,pe_no_col,po_no,afyp,aryp2 ORDER BY clos_ym";

	List<Map<String, Object>> result = db.queryList(vaSql);
	if (result != null) {
		return result;
	} else {
		return null;
	}
}
public List<Map<String, Object>> getPCByAnnual(String vaPeNo,String paYear) {

	 
	String vaSql = "SELECT clos_ym,pe_no_col,po_no,afyp ,aryp2 FROM "
			+ "(SELECT clos_ym,pe_no_col,po_no,afyp,aryp2 FROM tsli_ga_commission_positive p "
			+ "WHERE  dty_cd  IN ('S10240205','S10240206') "
			+ "AND clos_ym IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11') "
			+ "AND (COALESCE(pe_no_col, ' ') LIKE '"+vaPeNo+"') "
			+ "UNION ALL "
			+ "SELECT clos_ym,pe_no_col,po_no,-(afyp) AS afyp ,-(TO_NUMBER(aryp2)) AS aryp2 FROM tsli_ga_commission_negative p "
			+ "WHERE  dty_cd  IN ('S10240205','S10240206') "
			+ "AND clos_ym IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11') "
			+ "AND  (COALESCE(pe_no_col, ' ') LIKE '"+vaPeNo+"')) GROUP BY clos_ym,pe_no_col,po_no,afyp,aryp2 ORDER BY clos_ym";

	List<Map<String, Object>> result = db.queryList(vaSql);
	if (result != null) {
		return result;
	} else {
		return null;
	}
}


public String getVPPCQuarter(String paPeno,String paMonth1,String paMonth2,String paMonth3){
	
	String vaSql = "SELECT NVL(SUM(vp.PC),0) AS PC     "
			+ " FROM(SELECT ( (SUM(NVL(p.afYP,0))-SUM(NVL(n.aFYP,0)))+(SUM(NVL(p.arYP2,0))-SUM(NVL(n.arYP2,0))) ) as PC"
			+ " FROM TSLI_GA_COMMISSION_POSITIVE p LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE n ON p.pe_no_col = n.pe_no_col"
			+ "			 			                                    JOIN TSLI_GA_AGENT_STRUCTURE a ON a.pe_no = p.pe_no_col"
			+ " WHERE p.DTY_CD ='S10240201'"
			+ "			 			       AND p.PE_NO_COL = ?"
			+ "			 			       AND p.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')"
			+ " GROUP BY p.PE_NO_col,p.DTY_CD,a.pe_no,a.NM_TITLE_THAI,a.NM_THAI,a.SURNM_THAI"
			+ " UNION ALL            "
			+ " SELECT ( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			+ "	 					      FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_MM"
			+ "	 					      WHERE a.PE_NO_MM IN (SELECT PE_NO"
			+ "	 					    		  FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 					                                WHERE PE_NO_BM = TIER1"
			+ "	 			                                                                  AND PE_NO_BM = ?"
			+ "	 					                                GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD) "
			+ "	 			                                        AND b.CLOS_YM = a.CLOS_YM"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			+ "	 					      GROUP BY a.PE_NO_MM, a.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI"
			+ "	 					      Union All"
			+ "	 					      SELECT ( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			+ "	 					      FROM TSLI_GA_COMMISSION_POSITIVE a LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                        JOIN TSLI_GA_AGENT_STRUCTURE ag ON ag.pe_no = a.pe_no_col"
			+ "	 					      WHERE a.PE_NO_MM IS NULL"
			+ "	 			                                        AND a.PE_NO_BM = ?"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			+ "	 					      GROUP BY a.PE_NO_col , a.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI"
			+ " UNION ALL "
			+ " SELECT ( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			+ "	 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_col"
			+ "	 			                                  WHERE a.PE_NO_col IN (SELECT PE_NO"
			+ "	 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 			                                                          WHERE PE_NO_BM = TIER2"
			+ "	 			                                                                AND PE_NO_BM = ?"
			+ "	 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
			+ "	 			                                 GROUP BY a.PE_NO_col, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI"
			+ "	 					      Union All"
			+ "	 					         SELECT ( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			+ "	 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_col"
			+ "	 			                                  WHERE a.PE_NO_MM IN (SELECT PE_NO"
			+ "	 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 			                                                          WHERE PE_NO_BM = TIER2"
			+ "	 			                                                                AND PE_NO_BM = ?"
			+ "	 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
			+ "	 			                                 GROUP BY a.PE_NO_col, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI) vp";
	
	Map<String, Object> result = db.querySingle(vaSql,paPeno,paPeno,paPeno,paPeno,paPeno);
	  
	if (result.get("PC") != null && result.size() > 0) {
		return result.get("PC").toString();
	} else {
		return null;
	}	
}


public String getVPPCAnnual(String paPeno,String paYear){
	
	String vaSql = "SELECT NVL(SUM(vp.PC),0) AS PC     "
			+ " FROM(SELECT ( (SUM(NVL(p.afYP,0))-SUM(NVL(n.aFYP,0)))+(SUM(NVL(p.arYP2,0))-SUM(NVL(n.arYP2,0))) ) as PC"
			+ " FROM TSLI_GA_COMMISSION_POSITIVE p LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE n ON p.pe_no_col = n.pe_no_col"
			+ "			 			                                    JOIN TSLI_GA_AGENT_STRUCTURE a ON a.pe_no = p.pe_no_col"
			+ " WHERE p.DTY_CD ='S10240201'"
			+ "			 			       AND p.PE_NO_COL = ?"
			+ "			 			       AND p.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12')"
			+ " GROUP BY p.PE_NO_col,p.DTY_CD,a.pe_no,a.NM_TITLE_THAI,a.NM_THAI,a.SURNM_THAI"
			+ " UNION ALL            "
			+ " SELECT ( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			+ "	 					      FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_MM"
			+ "	 					      WHERE a.PE_NO_MM IN (SELECT PE_NO"
			+ "	 					    		  FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 					                                WHERE PE_NO_BM = TIER1"
			+ "	 			                                                                  AND PE_NO_BM = ?"
			+ "	 					                                GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD) "
			+ "	 			                                        AND b.CLOS_YM = a.CLOS_YM"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12') "
			+ "	 					      GROUP BY a.PE_NO_MM, a.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI"
			+ "	 					      Union All"
			+ "	 					      SELECT ( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			+ "	 					      FROM TSLI_GA_COMMISSION_POSITIVE a LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                        JOIN TSLI_GA_AGENT_STRUCTURE ag ON ag.pe_no = a.pe_no_col"
			+ "	 					      WHERE a.PE_NO_MM IS NULL"
			+ "	 			                                        AND a.PE_NO_BM = ?"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12') "
			+ "	 					      GROUP BY a.PE_NO_col , a.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI"
			+ " UNION ALL "
			+ " SELECT ( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			+ "	 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_col"
			+ "	 			                                  WHERE a.PE_NO_col IN (SELECT PE_NO"
			+ "	 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 			                                                          WHERE PE_NO_BM = TIER2"
			+ "	 			                                                                AND PE_NO_BM = ?"
			+ "	 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12')  "
			+ "	 			                                 GROUP BY a.PE_NO_col, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI"
			+ "	 					      Union All"
			+ "	 					         SELECT ( (SUM(NVL(a.afYP,0))-SUM(NVL(b.aFYP,0)))+(SUM(NVL(a.arYP2,0))-SUM(NVL(b.arYP2,0))) ) as PC"
			+ "	 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_col"
			+ "	 			                                  WHERE a.PE_NO_MM IN (SELECT PE_NO"
			+ "	 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 			                                                          WHERE PE_NO_BM = TIER2"
			+ "	 			                                                                AND PE_NO_BM = ?"
			+ "	 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12')  "
			+ "	 			                                 GROUP BY a.PE_NO_col, ag.DTY_CD,ag.NM_TITLE_THAI,ag.NM_THAI,ag.SURNM_THAI) vp";
	
	Map<String, Object> result = db.querySingle(vaSql,paPeno,paPeno,paPeno,paPeno,paPeno);
	  
	if (result.get("PC") != null && result.size() > 0) {
		return result.get("PC").toString();
	} else {
		return null;
	}	
}

public List<Map<String, Object>> getVPPCQuarterDetail(String paPeno,String paMonth1,String paMonth2,String paMonth3){
	
	String vaSql = "SELECT DISTINCT p.clos_ym,p.pe_no_col,p.po_no,p.afyp,p.aryp2"
			+ " FROM TSLI_GA_COMMISSION_POSITIVE p LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE n ON p.pe_no_col = n.pe_no_col"
			+ "			 			                                    JOIN TSLI_GA_AGENT_STRUCTURE a ON a.pe_no = p.pe_no_col"
			+ " WHERE p.DTY_CD ='S10240201'"
			+ "			 			       AND p.PE_NO_COL = ?"
			+ "			 			       AND p.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')"
			+ " UNION ALL            "
			+ " SELECT DISTINCT a.clos_ym,a.pe_no_col,a.po_no,a.afyp,a.aryp2"
			+ "	 					      FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_MM"
			+ "	 					      WHERE a.PE_NO_MM IN (SELECT PE_NO"
			+ "	 					    		  FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 					                                WHERE PE_NO_BM = TIER1"
			+ "	 			                                                                  AND PE_NO_BM = ?"
			+ "	 					                                GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD) "
			+ "	 			                                        AND b.CLOS_YM = a.CLOS_YM"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			+ "	 					      Union All"
			+ "	 					      SELECT DISTINCT a.clos_ym,a.pe_no_col,a.po_no,a.afyp,a.aryp2"
			+ "	 					      FROM TSLI_GA_COMMISSION_POSITIVE a LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                        JOIN TSLI_GA_AGENT_STRUCTURE ag ON ag.pe_no = a.pe_no_col"
			+ "	 					      WHERE a.PE_NO_MM IS NULL"
			+ "	 			                                        AND a.PE_NO_BM = ?"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"') "
			+ " UNION ALL "
			+ " SELECT DISTINCT a.clos_ym,a.pe_no_col,a.po_no,a.afyp,a.aryp2"
			+ "	 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_col"
			+ "	 			                                  WHERE a.PE_NO_col IN (SELECT PE_NO"
			+ "	 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 			                                                          WHERE PE_NO_BM = TIER2"
			+ "	 			                                                                AND PE_NO_BM = ?"
			+ "	 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')  "
			+ "	 					      Union All"
			+ "	 					         SELECT DISTINCT a.clos_ym,a.pe_no_col,a.po_no,a.afyp,a.aryp2"
			+ "	 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_col"
			+ "	 			                                  WHERE a.PE_NO_MM IN (SELECT PE_NO"
			+ "	 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 			                                                          WHERE PE_NO_BM = TIER2"
			+ "	 			                                                                AND PE_NO_BM = ?"
			+ "	 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paMonth1+"','"+paMonth2+"','"+paMonth3+"')";

	List<Map<String, Object>> result = db.queryList(vaSql,paPeno,paPeno,paPeno,paPeno,paPeno);
	  
	if (result!=null) {
		return result;
	} else {
		return null;
	}	
}


public List<Map<String, Object>> getVPPCAnnualDetail(String paPeno,String paYear){
	
	String vaSql = "SELECT DISTINCT p.clos_ym,p.pe_no_col,p.po_no,p.afyp,p.aryp2"
			+ " FROM TSLI_GA_COMMISSION_POSITIVE p LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE n ON p.pe_no_col = n.pe_no_col"
			+ "			 			                                    JOIN TSLI_GA_AGENT_STRUCTURE a ON a.pe_no = p.pe_no_col"
			+ " WHERE p.DTY_CD ='S10240201'"
			+ "			 			       AND p.PE_NO_COL = ?"
			+ "			 			       AND p.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12')"
			+ " UNION ALL            "
			+ " SELECT DISTINCT a.clos_ym,a.pe_no_col,a.po_no,a.afyp,a.aryp2"
			+ "	 					      FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_MM"
			+ "	 					      WHERE a.PE_NO_MM IN (SELECT PE_NO"
			+ "	 					    		  FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 					                                WHERE PE_NO_BM = TIER1"
			+ "	 			                                                                  AND PE_NO_BM = ?"
			+ "	 					                                GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER1,DTY_CD) "
			+ "	 			                                        AND b.CLOS_YM = a.CLOS_YM"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12') "
			+ "	 					      Union All"
			+ "	 					      SELECT DISTINCT a.clos_ym,a.pe_no_col,a.po_no,a.afyp,a.aryp2"
			+ "	 					      FROM TSLI_GA_COMMISSION_POSITIVE a LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                        JOIN TSLI_GA_AGENT_STRUCTURE ag ON ag.pe_no = a.pe_no_col"
			+ "	 					      WHERE a.PE_NO_MM IS NULL"
			+ "	 			                                        AND a.PE_NO_BM = ?"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12') "
			+ " UNION ALL "
			+ " SELECT DISTINCT a.clos_ym,a.pe_no_col,a.po_no,a.afyp,a.aryp2"
			+ "	 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_col"
			+ "	 			                                  WHERE a.PE_NO_col IN (SELECT PE_NO"
			+ "	 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 			                                                          WHERE PE_NO_BM = TIER2"
			+ "	 			                                                                AND PE_NO_BM = ?"
			+ "	 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12')  "
			+ "	 					      Union All"
			+ "	 					         SELECT DISTINCT a.clos_ym,a.pe_no_col,a.po_no,a.afyp,a.aryp2"
			+ "	 			                                  FROM TSLI_GA_COMMISSION_POSITIVE a"
			+ "	 			                                        LEFT JOIN TSLI_GA_COMMISSION_NEGATIVE b"
			+ "	 			                                        ON a.PE_NO_col = b.PE_NO_col AND b.CLOS_YM = a.CLOS_YM"
			+ "			 			                                                  JOIN TSLI_GA_AGENT_STRUCTURE ag "
			+ "			 			                                                  ON ag.pe_no = a.PE_NO_col"
			+ "	 			                                  WHERE a.PE_NO_MM IN (SELECT PE_NO"
			+ "	 			                                                          FROM TSLI_GA_AGENT_STRUCTURE "
			+ "	 			                                                          WHERE PE_NO_BM = TIER2"
			+ "	 			                                                                AND PE_NO_BM = ?"
			+ "	 			                                                          GROUP BY PE_NO,PE_NO_SM,PE_NO_MM,PE_NO_BM,TIER2)"
			+ "	 			                                        AND a.CLOS_YM IN ('"+paYear+"01','"+paYear+"02','"+paYear+"03','"+paYear+"04','"+paYear+"05','"+paYear+"06','"+paYear+"07','"+paYear+"08','"+paYear+"09','"+paYear+"10','"+paYear+"11','"+paYear+"12')";

	List<Map<String, Object>> result = db.queryList(vaSql,paPeno,paPeno,paPeno,paPeno,paPeno);
	  
	if (result!=null) {
		return result;
	} else {
		return null;
	}	
}
}
